package sy.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import sy.dao.ProductDaoI;
import sy.model.Tproduct;
import sy.pageModel.DataGrid;
import sy.pageModel.PageHelper;
import sy.pageModel.Product;
import sy.service.ProductServiceI;
import sy.util.excel.ExcelWorkSheet;

@Service
public class ProductServiceImpl implements ProductServiceI {

	@Autowired
	private ProductDaoI productDao;

	@Override
	public DataGrid dataGrid(Product product, PageHelper ph) {
		DataGrid dg = new DataGrid();
		List<Product> ul = new ArrayList<Product>();
		Map<String, Object> params = new HashMap<String, Object>();
		String hql = " from Tproduct t ";
		List<Tproduct> l = productDao.find(hql + whereHql(product, params) + orderHql(ph), params, ph.getPage(), ph.getRows());
		if (l != null && l.size() > 0) {
			for (Tproduct t : l) {
				Product u = new Product();
				BeanUtils.copyProperties(t, u);
				ul.add(u);
			}
		}
		dg.setRows(ul);
		dg.setTotal(productDao.count("select count(*) " + hql + whereHql(product, params), params));
		return dg;
	}

	private String whereHql(Product product, Map<String, Object> params) {
		String hql = "";
		if (product != null) {
			hql += " where 1=1 ";
			if (product.getName() != null) {
				hql += " and t.name like :name";
				params.put("name", "%%" + product.getName() + "%%");
			}
			if (product.getCode() != null) {
				hql += " and t.code like :code";
				params.put("code", "%%" + product.getCode() + "%%");
			}

		}
		return hql;
	}

	private String orderHql(PageHelper ph) {
		String orderString = "";
		if (ph.getSort() != null && ph.getOrder() != null) {
			orderString = " order by t." + ph.getSort() + " " + ph.getOrder();
		}
		return orderString;
	}

	@Override
	synchronized public void add(Product product) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("code", product.getCode());
		if (productDao.count("select count(*) from Tproduct t where t.code = :code", params) > 0) {
			throw new Exception("产品编码已存在！");
		} else {
			Tproduct tproduct = new Tproduct();
			BeanUtils.copyProperties(product, tproduct);
			productDao.save(tproduct);
		}
	}

	@Override
	public Product get(String id) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", id);
		Tproduct t = productDao.get("select distinct t from Tproduct t where t.id = :id", params);
		Product product = new Product();
		BeanUtils.copyProperties(t,product);
		return product;
	}

	@Override
	synchronized public void edit(Product product) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("id", product.getId());
		params.put("code", product.getCode());
		if (productDao.count("select count(*) from Tproduct t where t.code = :code and t.id != :id", params) > 0) {
			throw new Exception("产品编码已存在！");
		} else {
			Tproduct tproduct = productDao.get(Tproduct.class, product.getId());
			BeanUtils.copyProperties(product, tproduct);
		}
	}

	@Override
	public void delete(String id) {
		productDao.delete(productDao.get(Tproduct.class, id));
	}

	@Override
	public String saveproductwithExcel(MultipartFile excelFile) {
		String message = "导入失败";
		Workbook book = null;
		String filetype=excelFile.getContentType();
		// 判断文件类型
		if (filetype.equals("application/vnd.ms-excel")||filetype.equals("application/ms-excel")) {
			try {
				book = new HSSFWorkbook(excelFile.getInputStream());
			} catch (FileNotFoundException e) {
				message = "导入失败未检索到模版数据";
				e.printStackTrace();
			} catch (IOException e) {
				message = "导入失败";
				e.printStackTrace();
			}
		}else{
			return "文件类型错误！请选择excel导入！";
		}
		// book.getNumberOfSheets(); 判断Excel文件有多少个sheet
		Sheet sheet = book.getSheetAt(0);
		ExcelWorkSheet<Tproduct> excelWorkSheet = new ExcelWorkSheet<Tproduct>();
		// 保存工作单名称
		Row firstRow = sheet.getRow(0);
		Iterator<Cell> iterator = firstRow.iterator();
		// 保存列名
		List<String> cellNames = new ArrayList<String>();
		while (iterator.hasNext()) {
			cellNames.add(iterator.next().getStringCellValue());
		}
		excelWorkSheet.setColumns(cellNames);
		int m=0;    //记录导入信息与以存在信息的重复数量，即更新数。
		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			
			Tproduct product=null;
			String productCode="";
			Row ros = sheet.getRow(i);
			//设置产品编码
			if(ros.getCell(0) != null && !ros.getCell(0).toString().trim().equals("")){
				ros.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
				productCode=ros.getCell(0).getStringCellValue();
			}else{
				return "第"+(i+1)+"行产品编码不能为空";
			}
			product=productDao.get("from Tproduct t where t.code='"+productCode+"'");
			if(product!=null){
				m++;
			}else{
				product = new Tproduct();
				product.setId(UUID.randomUUID().toString());
				product.setCode(productCode);
			}
			//设置产品名称
			if(ros.getCell(1) != null&& !ros.getCell(1).toString().trim().equals("")){
				ros.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
				product.setName(ros.getCell(1).getStringCellValue());
			}
			//设置产品描述
			if(ros.getCell(2) != null&& !ros.getCell(2).toString().trim().equals("")){
				ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
				product.setDetail(ros.getCell(2).getStringCellValue());
			}
			//设置产品重量
			if(ros.getCell(3) != null && !ros.getCell(3).toString().trim().equals("")){
				ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
				try{
					product.setWeight(Integer.parseInt(ros.getCell(3).getStringCellValue()));
				}catch (Exception e){
					return "第"+(i+1)+"行产品重量错误，请填写数字！";
				}
			}else{
				return "第"+(i+1)+"行产品重量不能为空";
			}
			//设置产品体积
			if(ros.getCell(4) != null && !ros.getCell(4).toString().trim().equals("")){
				ros.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
				try{
					product.setVolume(Integer.parseInt(ros.getCell(4).getStringCellValue()));
				}catch (Exception e){
					return "第"+(i+1)+"行产品体积错误，请填写数字！";
				}
			}else{
				return "第"+(i+1)+"行产品体积不能为空";
			}
			//设置备注
			if(ros.getCell(5) != null && !ros.getCell(5).toString().trim().equals("")){
				ros.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
				product.setRemark(ros.getCell(5).getStringCellValue());
			}
			productDao.saveOrUpdate(product);
			int n=sheet.getLastRowNum()-m; //记录添加记录的数量。
			message="导入成功！添加了"+n+"条数据，更新了"+m+"条数据";
		}
		return message;
	}
}
